package com.goktech.project.hmw.dao.impl;

import com.goktech.project.hmw.dao.ICityDao;
import com.goktech.project.hmw.mapper.City;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class CityDaoImpl implements ICityDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	/**
	 * create by yej@goltech.cn
	 * description 通过象征获取数据
	 *
	 * @param name 乡镇名
	 * @return
	 */
	@Override
	public City getCity(String name) {
		List<City> list = jdbcTemplate.query("select * from city where name = ?", new Object[]{name}, new BeanPropertyRowMapper<>(City.class));
		if (list != null && list.size() > 0) {
			City city = list.get(0);
			return city;
		} else {
			return null;
		}
	}

	/**
	 * create by yej@goktech.cn
	 * decription 通过社区获取乡镇总金额
	 *
	 * @param county
	 * @return
	 */
	@Override
	public List<City> listCity(String county) {
		List<City> list = jdbcTemplate.query("select * from city where county = ?", new Object[]{county}, new BeanPropertyRowMapper<>(City.class));
		if (list != null && list.size() > 0) {
			return list;
		} else {
			return null;
		}
	}

	/**
	 * create by yej@goktech
	 * description 获取乡镇所有数据
	 *
	 * @return
	 */
	@Override
	public List<City> listAllCity() {
		List<City> list = jdbcTemplate.query("select * from city", new Object[]{}, new BeanPropertyRowMapper<>(City.class));
		if (list != null && list.size() > 0) {
			return list;
		} else {
			return null;
		}
	}

	/**
	 * 获取乡镇人数和经纬度
	 * @return
	 */
	@Override
	public List<City> listAllCityContainTotal() {
//		String sqlStr = " select sum(TOTAL_PERSON) as TOTAL_PERSON,city.COUNTY,city.`NAME`,MONEY,`PRECISION`,LATITUDE from city " +
//				" inner join county_township_person " +
//				" on city.COUNTY=county_township_person.COUNTY " +
//				" and city.`NAME`=county_township_person.TOWNSHIP " +
//				" GROUP BY city.COUNTY,city.`NAME`,MONEY,`PRECISION`,LATITUDE " +
//				" ORDER BY TOTAL_PERSON desc ";
		String sqlStr="select a.county,b.`name`,a.TOTAL_CAPITAL as money,a.TOTAL_PERSON,b.`PRECISION` ,b.LATITUDE from (SELECT county,township,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL,sum(TOTAL_PERSON) as TOTAL_PERSON FROM `all_in_one` GROUP BY county,township) a LEFT JOIN  city b on a.COUNTY = b.county and a.TOWNSHIP=b.`name`";
		List<City> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(City.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 *
	 * 获取全市发放总金额
	 * @return
	 */
	@Override
	public String total_money(){
		String sqlStr="select sum( convert(MONEY,DECIMAL(32,2))) as money from city;";
		List<City> list=jdbcTemplate.query(sqlStr,new Object[]{},new BeanPropertyRowMapper<>(City.class));
		if(list!=null&&list.size()>0){
			return list.get(0).getMoney();
		}else{
			return "0.00";
		}
	}
}
